package cz.matasek.dbLogger.sql;

import cz.matasek.dbLogger.Constant;
import cz.matasek.dbLogger.Registry;
import cz.matasek.dbLogger.database.DatabaseConnection;
import cz.matasek.dbLogger.exception.DatabaseErrorException;
import cz.matasek.dbLogger.exception.FileDoesNotExistException;
import cz.matasek.dbLogger.exception.FileIOException;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * An abstract representation of SQL statement.
 * Every statement is uniquely identified by its statement type and database
 * platform.
 * <p>Optionally, this class brings the possibility of keyword replacements.</p>
 * <p>All statements are cached, so that statements used more than once are
 * read from cache, not from the physical device.</p>
 *
 * @author Luboš Matásek
 */
public class SqlStatement {

    /**
     * SQL statement.
     */
    private String sql;
    /**
     * Target database.
     */
    private DatabaseConnection database;
    /**
     * Unique type of statement.
     */
    private SqlStatementType statementType;
    /**
     * Prepared SQL statement object.
     */
    private PreparedStatement preparedStatement;
    /**
     * SQL statement object.
     */
    private Statement statement;
    /**
     * Size of the file reading buffer.
     */
    private static int FILE_BUFFER_SIZE = 4096;

    /**
     * Creates a new SqlStatement of the specified name for target database.
     *
     * @param database target database
     * @param statementType unique statement type
     */
    public SqlStatement(DatabaseConnection database, SqlStatementType statementType) {
        this.database = database;
        this.statementType = statementType;

        // reads SQL sql
        this.readStatement();
    }

    /**
     * Reads specified file.
     * Can throw {@link cz.matasek.dbLogger.exception.FileDoesNotExistException}
     * if file does not exist.
     * Can throw {@link cz.matasek.dbLogger.exception.FileIOException} if an I/O
     * error occurs.
     *
     * @param filename name of the file
     * @param charset charset of the file
     * @return content of the file
     */
    private final String readFile(String filename, Charset charset) {
        // opens stream for reading file
        FileInputStream fis;
        try {
            fis = new FileInputStream(filename);
        } catch (FileNotFoundException ex) {
            throw new FileDoesNotExistException(new File(filename));
        }

        // opens stream fom reading with a specified charset
        InputStreamReader isr = new InputStreamReader(fis, charset);
        // opens stream for buffered reading
        BufferedReader br = new BufferedReader(isr);

        // reading buffer
        char[] buffer = new char[SqlStatement.FILE_BUFFER_SIZE];
        // content of the file
        StringBuilder fileContent = new StringBuilder();

        // reads the file
        try {
            while (br.read(buffer) != -1) {
                fileContent.append(buffer);
            }

            // closes all streams
            br.close();
            isr.close();
            fis.close();
        } catch (IOException ex) {
            throw new FileIOException(new File(filename));
        }

        // replaces all newlines (because Oracle PL/SQL blocks must be in
        // a single row and trims the whole statement
        return fileContent.toString().replaceAll("\r\n", "  ").trim();
    }

    /**
     * Reads SQL statement with specified name for target database.
     * If statement is already in cache, it is copied from there. Otherwise
     * it is read from file.
     */
    private final void readStatement() {
        if (Registry.statementsCache.containsKey(this.statementType)) {
            this.sql = Registry.statementsCache.get(this.statementType);
        } else {
            // name of the SQL script file
            String filename = Constant.SQL_SCRIPTS_DIRECTORY + File.separator +
                    this.database.getPlatformName() + File.separator +
                    this.statementType + Constant.SQL_FILE_EXTENSION;

            // reads script from a file
            this.sql = this.readFile(filename, Constant.SQL_FILE_CHARSET);

            // saves statement into cache
            Registry.statementsCache.put(this.statementType, this.sql);
        }
    }

    /**
     * Replaces all occurences of the keyword with the specified replacement
     * string.
     *
     * @param keyword keyword to be substituted
     * @param replacement text to be substituted for the keyword
     */
    public final void replaceKeyword(ReplacementKeyword keyword, String replacement) {
        this.sql =
                this.sql.replaceAll(keyword.toString(), replacement);
    }

    /**
     * Returns this SQL statement as string.
     *
     * @return SQL statement
     */
    public final String getSql() {
        return this.sql;
    }

    /**
     * Executes the SQL statement, which may be an INSERT, UPDATE, or DELETE
     * statement or an SQL statement that returns nothing, such as an SQL DDL
     * statement.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @return either (1) the row count for SQL Data Manipulation Language (DML)
     * statements or (2) 0 for SQL statements that return nothing
     */
    public final int executeUpdate() {
        try {
            // creates statement
            Statement stmt = this.database.getConnection().createStatement();

            // performs query
            int result = stmt.executeUpdate(this.sql);

            // closes statement
            stmt.close();

            // sends result
            return result;
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Prepares a PreparedStatement object for sending parameterized SQL
     * statements to the database.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     */
    public final void prepareStatement() {
        try {
            this.preparedStatement =
                    this.database.getConnection().prepareStatement(this.sql);
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Sets the designated parameter to the PreparedStatement object.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @param parameterIndex the first parameter is 1, the second is 2, ...
     * @param x the parameter value
     */
    public final void setString(int parameterIndex, String x) {
        try {
            this.preparedStatement.setString(parameterIndex, x);
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Sets the designated parameter to the PreparedStatement object.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @param parameterIndex the first parameter is 1, the second is 2, ...
     * @param x the parameter value
     */
//    public final void setDate(int parameterIndex, Date date) {
//        try {
//            this.preparedStatement.setDate(parameterIndex, date);
//        } catch (SQLException ex) {
//            throw new DatabaseErrorException(ex);
//        }
//    }
    
    /**
     * Executes this SQL statement, which returns a single ResultSet object.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @return a ResultSet object that contains the data produced by the given
     * query; never null
     */
    public final ResultSet executeQuery() {
        try {
            // performs either PreparedStatement or Statement
            if (this.preparedStatement == null) {
                // creates sql
                this.statement = this.database.getConnection().createStatement();

                // performs query
                return this.statement.executeQuery(this.sql);
            } else {
                return this.preparedStatement.executeQuery();
            }
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Closes currently opened statement.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     */
    public final void close() {
        try {
            if (this.statement != null) {
                this.statement.close();
            }

            if (this.preparedStatement != null) {
                this.preparedStatement.close();
            }
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }
}
